package cn.sypt.yuhao.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;


import org.junit.Test;

import cn.itcast.jdbc.TxQueryRunner;
import cn.sypt.yuhao.domain.ChartItems;
import cn.sypt.yuhao.domain.Charts;

public class ChartsDao {
	private QueryRunner qr = new TxQueryRunner();
	
	public List<Charts> findBy() {
		String sql = "select category.fid fid, category.fname name,category.fname id, count(*) y ,category.fname drilldown from video,category where video.fid=category.fid GROUP  BY category.fid";
		try {
			List<Charts> listchars = qr.query(sql, new BeanListHandler<Charts>(
					Charts.class));
			for (int i = 0; i < listchars.size(); i++) {
				
				String sql1 = "SELECT acategory.aname name,count(*) y from video,acategory,bcategory where "
						+ "video.bid=bcategory.bid and bcategory.aid=acategory.aid and acategory.fid=?"
						+ " GROUP BY acategory.aid";
				List<ChartItems> listitems=qr.query(sql1, new BeanListHandler<ChartItems>(ChartItems.class),listchars.get(i).getFid());
				listchars.get(i).setData(listitems);
			}
			return listchars;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public List<Charts> findBySC(String uid){
		String sql = "select category.fid fid, category.fname name,category.fname id, count(fname) y ,category.fname drilldown from video,category,zan where video.fid=category.fid and zan.spid=video.spid and zan.uid=? GROUP  BY category.fid";
		try {
			List<Charts> listchars = qr.query(sql, new BeanListHandler<Charts>(
					Charts.class),uid);
			for (int i = 0; i < listchars.size(); i++) {
				
				String sql1 = "SELECT acategory.aname name,count(*) y from video,acategory,bcategory,zan where "
						+ "video.bid=bcategory.bid and bcategory.aid=acategory.aid and video.spid=zan.spid and zan.uid=? and acategory.fid=?"
						+ " GROUP BY acategory.aid";
				List<ChartItems> listitems=qr.query(sql1, new BeanListHandler<ChartItems>(ChartItems.class),uid,listchars.get(i).getFid());
				listchars.get(i).setData(listitems);
			}
			return listchars;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public List<Charts> findByXZ(String uid){
		String sql = "select category.fid fid, category.fname name,category.fname id, count(fname) y ,category.fname drilldown from video,category,download where video.fid=category.fid and download.spid=video.spid and download.uid=? GROUP  BY category.fid";
		try {
			List<Charts> listchars = qr.query(sql, new BeanListHandler<Charts>(
					Charts.class),uid);
			for (int i = 0; i < listchars.size(); i++) {
				
				String sql1 = "SELECT acategory.aname name,count(*) y from video,acategory,bcategory,download where "
						+ "video.bid=bcategory.bid and bcategory.aid=acategory.aid and video.spid=download.spid and download.uid=? and acategory.fid=?"
						+ " GROUP BY acategory.aid";
				List<ChartItems> listitems=qr.query(sql1, new BeanListHandler<ChartItems>(ChartItems.class),uid,listchars.get(i).getFid());
				listchars.get(i).setData(listitems);
			}
			return listchars;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	
	public List<Charts> findByPL(String uid){
		String sql = "select category.fid fid, category.fname name,category.fname id, count(fname) y ,category.fname drilldown from video,category,comment where video.fid=category.fid and comment.spid=video.spid and comment.uid=? GROUP  BY category.fid";
		try {
			List<Charts> listchars = qr.query(sql, new BeanListHandler<Charts>(
					Charts.class),uid);
			for (int i = 0; i < listchars.size(); i++) {
				
				String sql1 = "SELECT acategory.aname name,count(*) y from video,acategory,bcategory,comment where "
						+ "video.bid=bcategory.bid and bcategory.aid=acategory.aid and video.spid=comment.spid and comment.uid=? and acategory.fid=?"
						+ " GROUP BY acategory.aid";
				List<ChartItems> listitems=qr.query(sql1, new BeanListHandler<ChartItems>(ChartItems.class),uid,listchars.get(i).getFid());
				listchars.get(i).setData(listitems);
			}
			return listchars;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	
	
}
